from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession, async_sessionmaker
from sqlalchemy.orm import declarative_base
from dotenv import load_dotenv
import os


# 加载环境变量
load_dotenv()

# 从环境变量获取数据库配置，提供更合理的默认值
DB_USER = os.getenv("DB_USER", "root")
DB_PASSWORD = os.getenv("DB_PASSWORD", "your_password")  #
DB_HOST = os.getenv("DB_HOST", "127.0.0.1")  # 使用127.0.0.1比localhost更可靠
DB_PORT = int(os.getenv("DB_PORT", "3306"))  # 确保端口是整数
DB_NAME = os.getenv("DB_NAME", "fastapi_db")

# 异步MySQL连接URL (使用aiomysql驱动)
SQLALCHEMY_DATABASE_URL = (
    f"mysql+aiomysql://{DB_USER}:{DB_PASSWORD}@{DB_HOST}:{DB_PORT}/{DB_NAME}"
    "?charset=utf8mb4"  # 添加字符集参数
)

# 创建异步引擎
engine = create_async_engine(
    SQLALCHEMY_DATABASE_URL,
    echo=False,  # 开发时显示SQL日志 True开启
    pool_size=5,  # 更合理的默认池大小
    max_overflow=10,
    pool_pre_ping=True,  # 连接前执行ping测试
    pool_recycle=3600,  # 1小时后回收连接
    connect_args={
        "connect_timeout": 5  # 5秒连接超时
    }
)

# 使用async_sessionmaker替代sessionmaker
AsyncSessionLocal = async_sessionmaker(
    bind=engine,
    class_=AsyncSession,
    expire_on_commit=False,
    autoflush=False
)

# 声明基类
Base = declarative_base()

# 异步依赖注入
async def get_db():
    async with AsyncSessionLocal() as session:
        try:
            yield session
            await session.commit()
        except Exception:
            await session.rollback()
            raise
        finally:
            await session.close()